'''
Created on 01/07/2010

@author: sbrito
'''

import sqlite3
from model.Candidate import Candidate
from lib.utils import log

CREATE_CANDIDATES_TABLE = """CREATE TABLE candidates
(uf_id text, type text, candidate_id text, img text, nome_urna text, numero text, nome_completo text, sexo text, data_nascimento text, estado_civil text,
nacionalidade text, naturalidade text, instrucao text, ocupacao text, site text, partido text, coligacao text, composicao_coligacao text,
cargo_que_concorre text, processo_protocolo text, resultado_eleicao text, julgamento_contas text, cnpj_campanha text)"""

CREATE_CANDIDATE_GOODS_TABLE = """CREATE TABLE candidate_goods
(candidate_id text, desc text, type text, value text)"""

DROP_CANDIDATES_TABLE = "DROP TABLE IF EXISTS candidates"
DROP_CANDIDATE_GOODS_TABLE = "DROP TABLE IF EXISTS candidate_goods"

INSERT_CANDIDATE = "INSERT INTO candidates VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
INSERT_GOOD = "INSERT INTO candidate_goods VALUES (?,?,?,?)"

SELECT_CANDIDATE_IDS = "SELECT candidate_id FROM candidates"
SELECT_CANDIDATE_FROM_STATE = "SELECT * FROM candidates WHERE uf_id=?"
SELECT_CANDIDATE_GOODS = "SELECT desc, type, value FROM candidate_goods WHERE candidate_id=?"

def connect(database_name):
    return sqlite3.connect(database_name)

def disconnect(connection):
    connection.close()

def create_tables(connection):
    c = connection.cursor()
    c.execute(DROP_CANDIDATES_TABLE)
    c.execute(DROP_CANDIDATE_GOODS_TABLE)
    c.execute(CREATE_CANDIDATES_TABLE)
    c.execute(CREATE_CANDIDATE_GOODS_TABLE)
    connection.commit()
    c.close()

def insert_candidates(connection, candidates):
    c = connection.cursor()
    
    for candidate in candidates:
        c.execute(INSERT_CANDIDATE, (
                   candidate.uf, candidate.candidate_type, candidate.candidate_id,
                   candidate.img, candidate.nome_urna, candidate.numero, candidate.nome_completo, candidate.sexo, candidate.data_nascimento, candidate.estado_civil, candidate.nacionalidade,
                   candidate.naturalidade, candidate.instrucao, candidate.ocupacao, candidate.site, candidate.partido, candidate.coligacao, candidate.composicao_coligacao,
                   candidate.cargo_que_concorre, candidate.processo_protocolo, candidate.resultado_eleicao, candidate.julgamento_contas, candidate.cnpj_campanha
                   ))
        
        for bem in candidate.bens:
            c.execute(INSERT_GOOD, (candidate.candidate_id, bem['desc'], bem['type'], bem['value']))
    
    connection.commit()
    c.close()
    
def get_candidate_ids(connection):
    c = connection.cursor()

    results = [e[0] for e in c.execute(SELECT_CANDIDATE_IDS).fetchall()]

    c.close()
    
    return results

def get_candidates_from_uf(connection, uf):
    c = connection.cursor()

    results = [Candidate(e) for e in c.execute(SELECT_CANDIDATE_FROM_STATE, (uf,)).fetchall()]
    
    for candidate in results:
        candidate.bens = get_candidate_goods(connection, candidate.candidate_id)
    
    #log(results)

    c.close()
    
    return results

def get_candidate_goods(connection, candidate_id):
    c = connection.cursor()

    results = [{'desc': good[0], 'type': good[1], 'value': good[2] } for good in c.execute(SELECT_CANDIDATE_GOODS, (candidate_id,)).fetchall()]
    
    #log(results)

    c.close()
    
    return list(results)